package com.vepick.metier.requetes;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import com.vepick.vue.interfaceClient.InterfaceClient;

/*
 * Classes pour g�rer les locations au ticket
 * Je ne red�taille pas tous, marche comme pour les abonn�s sauf pour la gestion des code VPlus
 */
public class Requetes_stationTicket extends Requetes_station {

	public static void effectuerLocationTicket(Connection conn, int idStation)
			throws SQLException {
		int lesVelo[][] = new int[1][3];
		String typeClassif = "std";
		String requete = "SELECT v.idVelo, b.idBornette, c.typeClassif FROM Bornette b JOIN VELO v on b.idVelo=v.idVelo"
				+ " JOIN STATION s on b.idStation=s.idStation"
				+ " LEFT OUTER JOIN (SELECT * FROM CLASSIFICATION WHERE sysdate between heureDebutClassif AND heureFinClassif) c ON s.idStation=c.idStation"
				+ " WHERE s.idStation ="
				+ idStation
				+ " AND v.etatVelo=1 AND v.localisationVelo='Bornette'";
		Statement stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery(requete);
		while (res.next()) {
			if (res.isFirst()) {
				lesVelo[0][0] = res.getInt("idVelo");
				lesVelo[0][1] = res.getInt("idBornette");
				typeClassif = res.getString("typeClassif");
				if(typeClassif!=null && typeClassif.trim().equals("Vplus")){
					lesVelo[0][2]=1;
				} else {
					lesVelo[0][2]=0;
				}
			}
		}
		res.close();
		// affichage de ce qui va �tre lou�...
		System.out.println("velo: " + lesVelo[0][0] + ", borne: "
				+ lesVelo[0][1] + ", classification: " + typeClassif);
		// et on part faire l'enregistrement de la location!
		enregistrerLocationTicket(conn, idStation, lesVelo[0][0],
				lesVelo[0][1], lesVelo[0][2]);
	}

	private static void enregistrerLocationTicket(Connection conn,
			int idStation, int idVelo, int idBornette, int idClassif)
			throws SQLException {
		int idClient = -1;
		idClient = Requetes_client.inscriptionTicket(conn);
		String requeteAjoutLoc = "INSERT INTO LOCATIONVELO VALUES(idLocation_seq.nextVal, "
				+ idStation
				+ ", null, "
				+ idVelo
				+ ", "
				+ idClient
				+ ", sysdate, null, null, " + idClassif + ")";
		String requeteMajVelo = "UPDATE VELO SET localisationVelo='Location' WHERE idVelo="
				+ idVelo;
		String requeteMajBorne = "UPDATE BORNETTE SET idVelo=null WHERE idBornette="
				+ idBornette;
		Statement stmt = conn.createStatement();
		stmt.executeUpdate(requeteAjoutLoc);
		stmt.executeUpdate(requeteMajVelo);
		stmt.executeUpdate(requeteMajBorne);
		conn.commit();
		InterfaceClient.ouvrirInterfaceClient();
	}

	public static void rendreLocationTicket(Connection conn, int idStation,
			int idClient, int etatVelo) throws SQLException {
		@SuppressWarnings("resource")
		Scanner sc = new Scanner(System.in);
		int idVelo = -1;
		int idBorne = -1;
		String typeClassif = "std";
		int bonusVPlus = -1;
		int idLocation = -1;
		int idVPlus = -1;
		String codeVPlus = null;
		Boolean reducVPlus = false;
		String testVPlus;
		double prixLoc = 0;
		int amende =0;
		int valeurBonus=0;

		System.out.println("Saisissez le numero du velo a rendre");
		idVelo = sc.nextInt();
		while (!reducVPlus) {
			System.out
					.println("Beneficiez vous d'un code de reduction VPlus? (O/N)");
			testVPlus = sc.next();
			if (testVPlus.equals("O")) {
				System.out
						.println("Saisissez l'identifiant associe a la reduction");
				idVPlus = sc.nextInt();
				System.out.println("Saisissez le code VPlus");
				codeVPlus = sc.next();
				reducVPlus = verifierCodeVPlus(conn, idVPlus, codeVPlus);
			} else {
				break;
			}
		}
		if(reducVPlus) valeurBonus=1;
		String requete = "SELECT idClient, bonusVPlus, idLocation FROM locationVelo WHERE idVelo="
				+ idVelo + " and FinLoc IS NULL and idClient=" + idClient;
		Statement stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery(requete);
		if (res.next()) {
			bonusVPlus = res.getInt("bonusVPlus");
			idLocation = res.getInt("idLocation");
			String requete2 = "SELECT b.idBornette, c.typeClassif FROM Bornette b JOIN STATION s on b.idStation=s.idStation LEFT OUTER JOIN (SELECT * FROM CLASSIFICATION WHERE sysdate between heureDebutClassif AND heureFinClassif) c ON s.idStation=c.idStation WHERE s.idStation ="
					+ idStation + " AND b.idVelo IS NULL AND b.etatBornette=1";
			res = stmt.executeQuery(requete2);
			while (res.next()) {
				idBorne = res.getInt("idBornette");
				typeClassif = res.getString("typeClassif");
			}
			//calcul de la dur�e en minute de la location puis du prix
			String requete3 = "SELECT (sysdate-debutLoc)*1440 FROM locationVelo where idLocation="+idLocation;
			res=stmt.executeQuery(requete3);
			while(res.next()){
				if ((Double.parseDouble((res.getString(1))))>720){
					System.out.println("Une amende a ete emise pour une location superieure a 12 heures!");
					amende = 1;
				}
				if((0.01*(Double.parseDouble((res.getString(1)))-(30*valeurBonus)))>0){
					prixLoc=(0.01*(Double.parseDouble((res.getString(1)))-(30*valeurBonus)))+(amende*100);
				}
			}
			res.close();
			System.out.println("velo: " + idVelo + ", borne: " + idBorne
					+ ", classification: " + typeClassif);
			System.out.println("La location vous coutera: "+((int)(prixLoc*100))/100+" euros");
			if (typeClassif != null && typeClassif.trim().equals("Vplus")
					&& bonusVPlus == 0)
				System.out.println("Vous beneficiez d'un bonus VPlus!");
			else
				bonusVPlus = 1;
			enregistrerRetour(conn, idStation, idVelo, idBorne, idClient,
					bonusVPlus, idLocation, prixLoc, etatVelo, reducVPlus, idVPlus);
		} else {
			System.out.println("Le numero de velo est incorrect");
		}
		res.close();
	}

	private static Boolean verifierCodeVPlus(Connection conn, int idVPlus,
			String codeVPlus) throws SQLException {
		String query = "SELECT * FROM CLIENTTICKET where idClient=" + idVPlus
				+ "AND codeVPlus='" + codeVPlus
				+ "' AND codeVPlus!='USED' AND dateValiditeTicket>=sysdate";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		if (rs.next()) {
			System.out.println("Prise en compte du code VPlus!");
			return true;
		} else {
			System.out.println("Code VPlus non valide!");
			return false;
		}
	}

	private static void enregistrerRetour(Connection conn, int idStation,
			int idVelo, int idBornette, int idClient, int bonusVPlus,
			int idLocation, double prixLoc, int etatVelo,boolean reducVPlus, int idVPlus) throws SQLException {
		String requeteRetourLoc = "UPDATE LOCATIONVELO SET idStationFin="
				+ idStation + ", finLoc=sysdate, prixLoc="+prixLoc+" WHERE idLocation=" + idLocation;
		String requeteMajVelo = "UPDATE VELO SET localisationVelo='Bornette', etatVelo="+etatVelo+" WHERE idVelo="
				+ idVelo;
		String requeteMajBorne = "UPDATE BORNETTE SET idVelo=" + idVelo
				+ " WHERE idBornette=" + idBornette;
		//Requete de pour bloquer la reutilisation d'un bonus
		String requeteMAJCode = "UPDATE CLIENTTICKET SET codeVPlus='USED' where idClient="+idVPlus;
		Statement stmt = conn.createStatement();
		stmt.executeUpdate(requeteRetourLoc);
		stmt.executeUpdate(requeteMajVelo);
		stmt.executeUpdate(requeteMajBorne);
		if(reducVPlus)stmt.executeUpdate(requeteMAJCode);
		if (bonusVPlus == 1) {
			int codeVPlus = (int) ((Math.random() * 10000) - 1);
			String requeteMajBonus = "UPDATE CLIENTTICKET SET codeVPlus="
					+ codeVPlus
					+ ", dateValiditeTicket=sysdate + interval '30' day WHERE idClient="
					+ idClient;
			stmt.executeUpdate(requeteMajBonus);
			System.out
					.println("Pour beneficier de votre code VPlus: identifiant: "
							+ idClient + ", code VPlus: " + codeVPlus);
		}
		conn.commit();
		InterfaceClient.ouvrirInterfaceClient();
	}
}
